| 亿级数据量场景下，如何优化数据库分页查询方法 | 您所在的位置：网站首页 › 游标分页查询 oracle java › 亿级数据量场景下，如何优化数据库分页查询方法 |
亿级数据量场景下，如何优化数据库分页查询方法
|
本文分享自华为云社区《大数据量性能优化之分页查询》，作者： JavaEdge。 刷帖子翻页需要分页查询，搜索商品也需分页查询。当遇到上千万、上亿数据量，怎么快速拉取全量数据呢？ 比如： 大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计 拥有百万千万粉丝的大v，给全部粉丝推送消息 案例常见错误写法 SELECT * FROM table where kid = 1342 and type = 1 order id asc limit 149420,20;典型的排序+分页查询： order by col limit N,OFFSET MMySQL 执行此类SQL时：先扫描到N行，再取 M行。N越大，MySQL需扫描更多数据定位到具体的N行，这会耗费大量的I/O成本和时间成本。 为什么上面的SQL写法扫描数据会慢？ t是个索引组织表，key idx_kid_type(kid,type) 符合kid=3 and type=1 的记录有很多行,我们取第 9，10行。 select * from t where kid =3 and type=1 order by id desc 8,2;对于Innodb，根据 idx_kid_type 二级索引里面包含的主键去查找对应行。 对百万千万级记录，索引大小可能和数据大小相差无几，cache在内存中的索引数量有限，而且二级索引和数据叶子节点不在同一物理块存储，二级索引与主键的相对无序映射关系，也会带来大量随机I/O请求，N越大越需遍历大量索引页和数据叶，需要耗费的时间就越久。 由于上面大分页查询耗时长，是否真的有必要完全遍历“无效数据”？若需要： limit 8，2跳过前面8行无关数据页的遍历，可直接通过索引定位到第9、10行，这样是不是更快？ 这就是延迟关联的核心思想：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据，而非通过二级索引获取主键再通过主键遍历数据页。 通过如上分析可得，通过常规方式进行大分页查询慢的原因，也知道了提高大分页查询的具体方法。 一般分页查询简单的 limit 子句。limit 子句声明如下： SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offsetlimit 子句用于指定 select 语句返回的记录数，注意： offset 指定第一个返回记录行的偏移量，默认为0 初始记录行的偏移量是0，而非1 rows 指定返回记录行的最大数量 rows 为 -1 表示检索从某个偏移量到记录集的结束所有的记录行。若只给定一个参数：它表示返回最大的记录行数目。 实例： select * from orders_history where type=8 limit 1000,10;从 orders_history 表查询offset: 1000开始之后的10条数据，即第1001条到第1010条数据（1001 ='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id; +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 20 | | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | | | 2 | DERIVED | relation | index | ind_endtime | PRIMARY | 8 | NULL | 733552 | | +----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+ 执行时间: 20 rows in set (0.36 sec)优化后 执行时间 为原来的1/3 。 书签首先获取符合条件的记录的最大 id和最小id(默认id是主键) select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;根据id 大于最小值或者小于最大值进行遍历。 select xx,xx from t where kid=2333 and type=1 and id >=min_id order by id asc limit 100; select xx,xx from t where kid=2333 and type=1 and id = 1000001 limit 100;还可以使用 in，这种方式经常用在多表关联时进行查询，使用其他表查询的id集合，来进行查询： select * from order_history where id in (select order_id from trade_2 where goods = 'pen') limit 100; 临时表已经不属于查询优化，这儿附带提一下。 对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。 数据表的id一般在DB建立表时，强制为每一张表添加 id 递增字段，方便查询。 像订单库等数据量很大，一般会分库分表。这时不推荐使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器，并在数据表中使用另外的字段来存储这个唯一标识。 先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *。 参考segmentfault.com/a/119000003… 点击关注，第一时间了解华为云新鲜技术~ |
【本文地址】